Spreadsheet Assurance by "Control Around" is a Viable Alternative to the Traditional Approach

The traditional approach to spreadsheet auditing generally consists of auditing every distinct formula within a spreadsheet. Although tools are developed to support auditors during this process, the approach is still very time consuming and therefore relatively expensive. As an alternative to the traditional 'control through' approach, this paper discusses a 'control around' approach. Within the proposed approach not all distinct formulas are audited separately, but the relationship between input data and output data of a spreadsheet is audited through comparison with a shadow model developed in a modelling language. Differences between the two models then imply possible errors in the spreadsheet. This paper describes relevant issues regarding the 'control around' approach and the circumstances in which this approach is preferred above a traditional spreadsheet audit approach. 1 THE TRADITIONAL AUDIT APPROACH CAN BE VERY TIME CONSUMING 1.1 For moderately sized spreadsheets, traditional spreadsheet audit efforts are in the range of 25 to 75 hours A moderately sized spreadsheet typically involves thousands of cells. Spreadsheet Audit (SA) tools like OAK [1] and Spreadsheet Professional [2], reduce the audit to the inspection of the distinct formulas only. The number of distinct formulas strongly depends on the nature of the spreadsheet, but for moderately sized spreadsheets this number typically lies in the range of 500 to 1,500. Besides the mentioned SA tools, features in spreadsheet programs like “trace dependents” and “trace precedents” in Microsoft Excel, help the spreadsheet auditor to validate distinct formulas quickly. But still, as a rule of thumb, the inspection of a distinct formula by an experienced auditor takes on average around 3 minutes. The total effort of such a traditional spreadsheet audit, or in assurance terms control through approach, generally takes from 25 up to 75 hours. This amount of time is often out of proportion compared to the development of the spreadsheet. On the other hand, in many cases the assurance of the spreadsheet is still required by users or third parties, which decide to invest large sums of money based on the spreadsheet calculation. 1.2 For large spreadsheets, traditional SA often is not an option because throughput time is too long If the number of distinct formulas is much larger 1,500, then not only the time spend and the audit costs are an obstacle for following the traditional SA approach, but also the throughput time. For complicated, badly structured spreadsheets, it can be hard to perform the SA by more than one auditor in parallel. An example of such a complicated, badly structured spreadsheet is a spreadsheet with on one worksheet thousands of distinct and repetitive formulas. Consequently, the throughput time for these spreadsheets will be, based on the previous assumptions, more than two weeks. Because users want to take the spreadsheet in production as quick as possible the time-pressure on the SA is very high, and therefore often limited to one or two weeks.